# -*- encoding: utf-8 -*-
"""
@File    : data_manager.py
@Time    : 2021/10/25 9:13
@Author  : Coco
"""
import configparser
from sqlalchemy.exc import InvalidRequestError
from sqlalchemy import create_engine, Table, MetaData
from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker


class SqlOption:
    def __init__(self, database_path):
        # 读配置文件
        config = configparser.ConfigParser()
        config.read("D:\PyCharmProject\HS_ATS_VS\config\config.ini", encoding="utf-8")
        # 创建数据库连接
        self.engine = create_engine(f'sqlite:///{database_path}?check_same_thread=False')
        self.Base = declarative_base()
        self.Base.metadata.reflect(self.engine)
        self.metadata = MetaData(self.engine)
        self.session = sessionmaker(bind=self.engine)()
        # 配置文件中需求表的列表
        self.except_table_list = eval(config.get("database", "table_list"))
        # 当前数据库中现有的表
        self.current_table_name_list = list(self.Base.metadata.tables.keys())
        # 表名-表对象映射字典
        self.mode_dict = {}
        # 初始化所有表，该创建创建，该获取对象获取对象
        self._table_init()

    def _table_init(self):
        # 等待创建数据表的列表
        wait_create_table_list = []
        # 遍历配置文件中 需要的表名，如果当前不存在该表，则加入到待创建表的列表中
        for table in self.except_table_list:
            if table not in self.current_table_name_list:
                wait_create_table_list.append(table)
        # 如果当前表中不存在 history 表，也加到待建列表中
        if 'history' not in self.current_table_name_list:
            wait_create_table_list.append('history')
        # 创建所有待建列表中的表
        self._create_all_table(wait_create_table_list)
        # 初始化除历史表外所有表

        for table in self.except_table_list:
            try:
                exec("""self.{0} = Table('{0}', self.metadata,
                                Column('id', Integer, primary_key=True),
                                Column('test_mode', String),
                                Column('wafer_id', String),
                                Column('test_time', String),
                                Column('die_id', String),
                                Column('subdie_id', String),
                                Column('position', String),
                                Column('test_data', String),
                                Column('selected', String, server_default='0'))""".format(table))
            except InvalidRequestError:
                pass
            self.mode_dict[table] = eval("self.{}".format(table))
        try:
            # 初始化历史表
            self.his_db = Table('history', self.metadata,
                                Column('id', Integer, primary_key=True),
                                Column('table_name', String),
                                Column('wafer_id', String),
                                Column('test_time', String),
                                Column('test_mode', String),
                                Column('operate_count', String),
                                Column('edit_time', String),
                                Column('opt_data', String),
                                Column('select_list', String))
        except InvalidRequestError:
            pass
        self.mode_dict["history"] = self.his_db

    def _create_all_table(self, table_name_list):
        # 如果历史表需要创建，先创建历史表

        if "wafer" in table_name_list:
            self.his_db = Table('wafer', self.metadata,
                                Column('wafer_id', Integer, primary_key=True),
                                Column('wafer_number', String),
                                Column('shot_position', String),
                                Column('oem', String),
                                Column('batch', String),
                                Column('chip_number', String),
                                Column('test_time', String),
                                Column('temperature', String),
                                Column('humidity', String))

        elif "chip" in table_name_list:
            self.his_db = Table('chip', self.metadata,
                                Column('chip_id', Integer, primary_key=True),
                                Column('wafer_id', Integer, foreign_key=ForeignKey("user.id")),
                                Column('test_time', String),
                                Column('test_mode', String),
                                Column('operate_count', String),
                                Column('edit_time', String),
                                Column('opt_data', String),
                                Column('select_list', String))

        # if 'history' in table_name_list:
        #     self.his_db = Table('history', self.metadata,
        #                         Column('id', Integer, primary_key=True),
        #                         Column('table_name', String),
        #                         Column('wafer_id', String),
        #                         Column('test_time', String),
        #                         Column('test_mode', String),
        #                         Column('operate_count', String),
        #                         Column('edit_time', String),
        #                         Column('opt_data', String),
        #                         Column('select_list', String))
        #     # 将历史表从待建表中移除
        #     table_name_list.remove('history')
        # # 创建其他表
        # for table_name in table_name_list:
        #     exec("""self.{0} = Table('{0}', self.metadata,
        #                         Column('id', Integer, primary_key=True),
        #                         Column('test_mode', String),
        #                         Column('wafer_id', String),
        #                         Column('test_time', String),
        #                         Column('die_id', String),
        #                         Column('subdie_id', String),
        #                         Column('position', String),
        #                         Column('test_data', String),
        #                         Column('selected', String, server_default='0'))""".format(table_name))
        self.metadata.create_all(self.engine)

    def check_tables(self):
        self.metadata.reflect(self.engine)
        table_list = self.metadata.tables
        return table_list

    def insert_data(self, recv_list, table_name):
        table_obj = eval(f"self.{table_name}")
        exec("self.session.execute({}.insert(), {})".format(table_obj, recv_list))
        self.session.commit()

    def insert_history(self, table_name, wafer_id, test_time, test_mode, operate_count, edit_time, opt_data,
                       select_list):
        new_obj = {"table_name": table_name, "wafer_id": wafer_id, "operate_count": operate_count,
                   "test_time": test_time, "edit_time": edit_time, "test_mode": test_mode, "opt_data": opt_data,
                   "select_list": select_list}
        self.session.execute(self.his_db.insert(), new_obj)
        self.session.commit()

    def del_history(self, edit_time):
        self.session.query(self.his_db).filter_by(edit_time=edit_time).delete()
        self.session.commit()

    def get_history(self, edit_time):
        reflect_history_data = self.session.query(self.his_db).filter_by(edit_time=edit_time).first()
        select_mode = reflect_history_data[4]
        reflect_data = reflect_history_data[7]
        select_list = reflect_history_data[8]
        return select_mode, reflect_data, select_list

    def del_wafer(self, table_name, wafer_id):
        self.session.query(self.mode_dict[table_name]).filter_by(wafer_id=wafer_id).delete()
        self.session.query(self.his_db).filter_by(wafer_id=wafer_id).delete()
        self.session.commit()

    def del_test_time(self, table_name, wafer_id, test_time):
        self.session.query(self.mode_dict[table_name]).filter_by(wafer_id=wafer_id, test_time=test_time).delete()
        self.session.query(self.his_db).filter_by(wafer_id=wafer_id, test_time=test_time).delete()
        self.session.commit()

    def get_wafer_id_by_mode(self, table_name):
        wafer_list = []
        for item in self.session.query(self.mode_dict[table_name]).group_by('wafer_id').all():
            if item[2] not in wafer_list:
                wafer_list.append(item[2])
        return wafer_list

    def get_date_by_wafer_id_and_mode(self, table_name, wafer_id):
        date_list = []
        for item in self.session.query(self.mode_dict[table_name]).filter_by(
                wafer_id='{}'.format(wafer_id)).all():
            if item[3] not in date_list:
                date_list.append(item[3])
        return date_list

    def reflect_test_mode(self, table_name, wafer_id, date):
        mode_list = []
        for item in self.session.query(self.mode_dict[table_name]).filter_by(wafer_id=wafer_id,
                                                                             test_time='{}'.format(date)).all():
            if item[1] not in mode_list:
                mode_list.append(item[1])
        return mode_list

    def get_die_id_by_mode(self, table_name, wafer_id, date, mode):
        data = self.session.query(self.mode_dict[table_name]).filter_by(wafer_id=wafer_id, test_time='{}'.format(date),
                                                                        test_mode=mode).all()
        ret_dict = {'die_list': [], 'status_list': []}
        for item in data:
            die_sub_die = "{}-{}".format(item[4], item[5])
            ret_dict['die_list'].append(die_sub_die)
            ret_dict['status_list'].append(item[8])
        return ret_dict

    def get_data(self, table_name, wafer_id, date, die_id, subdie_id, test_mode):
        res = self.session.query(self.mode_dict[table_name]).filter_by(wafer_id=wafer_id,
                                                                       test_time='{}'.format(date),
                                                                       die_id=die_id,
                                                                       test_mode=test_mode,
                                                                       subdie_id=subdie_id).all()
        ret_dict = {'test_mode': res[0][1], 'wafer_id': res[0][2], 'die_sub_die': res[0][6], 'data': res[0][7],
                    'selected': res[0][8]}
        return ret_dict

    def change_checked_status(self, table_name, wafer_id, date, test_mode, die_id, subdie_id):
        self.session.query(self.mode_dict[table_name]).filter_by(wafer_id=wafer_id,
                                                                 test_time='{}'.format(date),
                                                                 test_mode=test_mode,
                                                                 die_id=die_id,
                                                                 subdie_id=subdie_id).update({'selected': '1'})
        self.session.commit()

    def change_unchecked_status(self, table_name, wafer_id, date, test_mode, die_id, subdie_id):
        self.session.query(self.mode_dict[table_name]).filter_by(wafer_id=wafer_id,
                                                                 test_time='{}'.format(date),
                                                                 test_mode=test_mode,
                                                                 die_id=die_id,
                                                                 subdie_id=subdie_id).update({'selected': '0'})
        # 数据模型的修改已被自动加入session中
        self.session.commit()

    def change_all_status(self, table_name, wafer_id, date):
        self.session.query(self.mode_dict[table_name]).filter_by(wafer_id=wafer_id,
                                                                 test_time='{}'.format(date)).update({'selected': '0'})
        # 数据模型的修改已被自动加入session中
        self.session.commit()


if __name__ == '__main__':
    sql_opt = SqlOption("offline.db")
    fake_list = []
    for i in range(3):
        for j in range(4):
            test_dict = {"wafer_id": '000{}'.format(i), "die_id": "{}".format(j), "subdie_id": '0',
                         "position": '({},0)'.format(j), "test_mode": "V",
                         "test_data": '{}.{}'.format(1, 2),
                         "test_time": "2022-03-2{} 12:12:32".format(i)}
            fake_list.append(test_dict)
    sql_opt.insert_data(fake_list, "wafer")
